IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveMachineInfoFromWMI' AND type = 'P')
     DROP PROCEDURE dbo.SaveMachineInfoFromWMI
GO

CREATE PROCEDURE dbo.SaveMachineInfoFromWMI
     @PackageExecutionId bigint
AS
/*
 *
 * SaveMachineInfoFromWMI
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      We perform a lot of work here, this is due to the extreme
 *               efficiencies that we can get by copying the data down to temp
 *               tables then processing it here.  The SSIS package logic 
 *               required to perform the same operations was far too complex
 *               and hard to maintain.  Much of the WMI data has to be joined
 *               to other WMI data to get a final result.  The logic for the 
 *               "delete" operations was particularly tricky and inefficient.
 *               We collect up data then process it en mass.
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 10/10/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     -- Build working tables
     DECLARE @MissingNodes    TABLE ( MachineId int NOT NULL, MachineName varchar(255) NOT NULL )
     DECLARE @MissingClusters TABLE ( MachineId int NOT NULL, MachineName varchar(255) NOT NULL )

     DECLARE @MachineInfo TABLE (
               [RecId] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
               [MachineName] [varchar](128) NOT NULL,
               [ClusterVirtualNameFlag] [tinyint] NOT NULL,
               [PackageExecutionId] [bigint] NOT NULL,
               [OSEdition] [varchar](128) NULL,
               [OSLevel] [varchar](30) NULL,
               [OSArchitecture] [varchar](10) NULL,
               [OSVersion] [varchar](30) NULL,
               [MaxClockSpeed] [int] NULL,
               [PhysicalCPUCount] [int] NULL,
               [PhysicalCPUCoreCount] [int] NULL,
               [LogicalCPUCount] [int] NULL,
               [TotalMachineMemoryMB] [int] NULL )

     DECLARE @ClusterVolumeWithMountPoint TABLE (
               [RecId] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	          [MachineId] [int] NOT NULL,
	          [MachineName] [varchar](128) NOT NULL,
	          [ClusterVirtualNameFlag] [tinyint] NOT NULL,
	          [PackageExecutionId] [bigint] NOT NULL,
	          [DiskId] [varchar](128) NOT NULL,
	          [ClusterDiskName] [varchar](128) NOT NULL,
	          [DiskPartitionPath] [varchar](255) NOT NULL,
	          [VolumeGUID] [varchar](38) NOT NULL,
	          [VolumeLabel] [varchar](128) NOT NULL,
	          [SerialNumber] [bigint] NOT NULL )

     DECLARE @VolumeWithMountPoint TABLE (
               [RecId] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	          [MachineId] [int] NOT NULL,
	          [MachineName] [varchar](128) NOT NULL,
	          [ClusterVirtualNameFlag] [tinyint] NOT NULL,
	          [PackageExecutionId] [bigint] NOT NULL,
	          [DeviceID] [varchar](128) NOT NULL,
	          [Name] [varchar](128) NULL,
	          [Label] [varchar](32) NULL,
	          [BlockSize] [int] NULL,
	          [Capacity] [bigint] NULL,
	          [FreeSpace] [bigint] NULL,
	          [CompressedFlag] [int] NULL,
	          [DriveTypeDesc] [varchar](30) NULL,
	          [SerialNumber] [bigint] NULL,
	          [FileSystem] [varchar](6) NULL,
	          [MountPoint] [varchar](128) NULL )

     DECLARE @EnvVar TABLE (
               [RecId] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	          [MachineId] [int] NOT NULL,
	          [MachineName] [varchar](128) NOT NULL,
	          [ClusterVirtualNameFlag] [tinyint] NOT NULL,
	          [PackageExecutionId] [bigint] NOT NULL,
	          [VarName] [varchar](100) NOT NULL,
	          [VarUser] [varchar](128) NOT NULL,
	          [VarValue] [varchar](8000) NULL )

     DECLARE @RV                   int,
             @RecId                int,
             @MachineName          varchar(128),
             @ClusterFlag          tinyint,
             @MachineId            int,
             @MachineDriveVolumeId int,
             @VolumeDeviceId       varchar(128),
             @VolumeMountPoint     varchar(255),
             @VolumeBlockSize      int,
             @VolumeCapacity       bigint,
             @VolumeFreeSpace      bigint,
             @VolumeCompressedFlag tinyint,
             @VolumeDriveTypeDesc  varchar(30),
             @VolumeFileSystemType varchar(6),
             @VolumeLabel          varchar(32),
             @VolumeName           varchar(128),
             @VolumeSerialNumber   bigint,
             @VarName              varchar(100),
             @VarUser              varchar(128),
             @VarValue             varchar(8000),
             @PhysicalCPUCount     int,
             @PhysicalCPUCoreCount int,
             @LogicalCPUCoreCount  int,
             @MaxCPUSpeedMHz       int,
             @OSVersion            varchar(30),
             @OSLevel              varchar(30),
             @OSArchitecture       varchar(10),
             @OSEdition            varchar(128),
             @TotalMachineMemoryMB int

     SET @RV = 0 -- We explicitly do this instead of setting it when we declare
                 -- for maximum compatibility.

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- Machines
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------

     -- The question arose why don't we capture new machines from the SaveMachine
     -- process, the reason is that to get into that list you already have to 
     -- have existed in the Machine table.  There are 3 ways to get records into
     -- that table, the Server Info process can detect other machines, typically
     -- cluster nodes that are available to a SQL Instance, the cluster process
     -- here can detect nodes that are part of a cluster but may or may not be 
     -- involved with SQL for one or any node, and manually using the Save sproc.
     -- Given this, this process will only return machines that were detected
     -- here that were part of a clustere but not already added by the Server
     -- Info process.  

     -- Fill working table
     INSERT @MachineInfo
          ( MachineName,
            ClusterVirtualNameFlag,
            PackageExecutionId,
            OSEdition,
            OSLevel,
            OSArchitecture,
            OSVersion,
            MaxClockSpeed,
            PhysicalCPUCount,
            PhysicalCPUCoreCount,
            LogicalCPUCount,
            TotalMachineMemoryMB )
     SELECT oi.MachineName,
            ClusterVirtualNameFlag = 0,
            oi.PackageExecutionId,
            OSEdition              = oi.Caption,
            OSLevel                = oi.CSDVersion,
            OSArchitecture         = oi.OSArchitecture,
            OSVersion              = oi.Version,
            MaxClockSpeed          = ccci.MaxClockSpeed,
            PhysicalCPUCount       = cci.NumberOfProcessors,
            PhysicalCPUCoreCount   = ccci.PhysicalCPUCoreCount,
            LogicalCPUCount        = cci.NumberOfLogicalProcessors,
            TotalMachineMemoryMB   = oi.TotalVisibleMemorySize
     FROM   tempdb.dbo.tempGetMachineInfoOSInfo oi INNER JOIN tempdb.dbo.tempGetMachineInfoCPUCntInfo cci
               ON oi.PackageExecutionId = cci.PackageExecutionId
              AND oi.MachineName        = cci.MachineName
            INNER JOIN ( SELECT PackageExecutionId, 
                                MachineName,
                                MaxClockSpeed = MAX( MaxClockSpeed ),
                                PhysicalCPUCoreCount = SUM(NumberOfCores)
                         FROM   tempdb.dbo.tempGetMachineInfoCPUInfo
                         WHERE  PackageExecutionId = @PackageExecutionId
                         GROUP BY PackageExecutionId, MachineName ) ccci
               ON oi.PackageExecutionId = ccci.PackageExecutionId
              AND oi.MachineName        = ccci.MachineName
     WHERE  oi.PackageExecutionId    = @PackageExecutionId

     -- Get Cluster Name from Machine if it is already there
     UPDATE @MachineInfo
     SET    ClusterVirtualNameFlag = 1
     FROM   @MachineInfo mi INNER JOIN dbo.Machine m
               ON mi.MachineName = m.MachineName
     WHERE  m.ClusterVirtualNameFlag = 1

     -- Also Try the WMI Cluster Table
     UPDATE @MachineInfo
     SET    ClusterVirtualNameFlag = 1
     FROM   @MachineInfo mi INNER JOIN tempdb.dbo.tempGetMachineInfoCluster c
               ON mi.MachineName = c.ClusterName

     -- Walk Through the machines first, we'll do cluster stuff later.
     DECLARE Machine CURSOR FOR SELECT RecId
                                FROM   @MachineInfo
                                WHERE  ClusterVirtualNameFlag = 0

     OPEN Machine

     FETCH NEXT FROM Machine INTO @RecId

     WHILE ( @@FETCH_STATUS = 0 )
     BEGIN
          SELECT @MachineName          = m.MachineName,
                 @PhysicalCPUCount     = m.PhysicalCPUCount,
                 @PhysicalCPUCoreCount = m.PhysicalCPUCoreCount,
                 @LogicalCPUCoreCount  = m.LogicalCPUCount,
                 @MaxCPUSpeedMHz       = m.MaxClockSpeed,
                 @OSVersion            = m.OSVersion,
                 @OSLevel              = m.OSLevel,
                 @OSArchitecture       = m.OSArchitecture,
                 @OSEdition            = m.OSEdition,
                 @TotalMachineMemoryMB = m.TotalMachineMemoryMB
          FROM   @MachineInfo m
          WHERE  RecId = @RecId

          EXEC dbo.SaveMachine
                    @PackageExecutionId          = @PackageExecutionId,
                    @ComputerNamePhysicalNetBIOS = @MachineName,
                    @MachineName                 = @MachineName,
                    @FQMachineName               = NULL,
                    @PhysicalCPUCount            = @PhysicalCPUCount,
                    @PhysicalCPUCoreCount        = @PhysicalCPUCoreCount,
                    @LogicalCPUCoreCount         = @LogicalCPUCoreCount,
                    @MaxCPUSpeedMHz              = @MaxCPUSpeedMHz,
                    @OSVersion                   = @OSVersion,
                    @OSLevel                     = @OSLevel,
                    @OSArchitecture              = @OSArchitecture,
                    @OSEdition                   = @OSEdition,
                    @TotalMachineMemoryMB        = @TotalMachineMemoryMB,
                    @SuppressResultSetFlag       = 1

          FETCH NEXT FROM Machine INTO @RecId
     END

     CLOSE Machine
     DEALLOCATE Machine

     -- Get Missing Cluster Nodes
     INSERT dbo.Machine
          ( PackageExecutionId,
            EnabledFlag,
            MachineName,
            FQMachineName,
            ClusterVirtualNameFlag,
            LastModDt )
     OUTPUT INSERTED.MachineId, INSERTED.MachineName INTO @MissingNodes
     SELECT DISTINCT 
            @PackageExecutionId,
            1, -- EnabledFlag
            c.NodeName,
            c.NodeName,
            0, --ClusterVirtualNameFlag
            CURRENT_TIMESTAMP
     FROM   tempdb.dbo.tempGetMachineInfoClusterToNode c
     WHERE  NOT EXISTS ( SELECT 1
                         FROM   dbo.Machine m
                         WHERE  c.NodeName = m.MachineName
                         OR     c.NodeName = m.FQMachineName )
     AND    c.PackageExecutionId = @PackageExecutionId

     -- Get Missing Cluster Virtual Names
     INSERT dbo.Machine
          ( PackageExecutionId,
            EnabledFlag,
            MachineName,
            FQMachineName,
            ClusterVirtualNameFlag,
            LastModDt )
     OUTPUT INSERTED.MachineId, INSERTED.MachineName INTO @MissingClusters
     SELECT DISTINCT 
            @PackageExecutionId,
            1, -- EnabledFlag
            c.ClusterName,
            c.ClusterName,
            1, --ClusterVirtualNameFlag
            CURRENT_TIMESTAMP
     FROM   tempdb.dbo.tempGetMachineInfoCluster c
     WHERE  NOT EXISTS ( SELECT 1
                         FROM   dbo.Machine m
                         WHERE  c.ClusterName = m.MachineName
                         OR     c.ClusterName = m.FQMachineName )
     AND    c.PackageExecutionId = @PackageExecutionId

     -- Assign Machines To Clusters
     INSERT dbo.MachineCluster
          ( ClusterMachineId,
            MemberMachineId,
            PackageExecutionId,
            LastModDt )
     SELECT wmc.ClusterMachineId,
            wmc.MemberMachineId,
            @PackageExecutionId,
            CURRENT_TIMESTAMP     
     FROM   ( SELECT DISTINCT 
                     ClusterMachineId = cm.MachineId,
                     MemberMachineId  = mm.MachineId
              FROM   dbo.Machine cm INNER JOIN tempdb.dbo.tempGetMachineInfoCluster tc
                        ON cm.MachineName = tc.ClusterName
                     INNER JOIN tempdb.dbo.tempGetMachineInfoClusterToNode tcn
                        ON tcn.ClusterName = tc.ClusterName
                     INNER JOIN dbo.Machine mm
                        ON mm.MachineName = tcn.NodeName
              WHERE  tc.PackageExecutionId  = @PackageExecutionId
              AND    tcn.PackageExecutionId = @PackageExecutionId ) wmc
     WHERE  NOT EXISTS ( SELECT 1
                         FROM   dbo.MachineCluster mc
                         WHERE  mc.ClusterMachineId = wmc.ClusterMachineId
                         AND    mc.MemberMachineId  = wmc.MemberMachineId )

     -- Remove Machines Not in a cluster anymore
     DELETE dbo.MachineCluster
     FROM   dbo.MachineCluster mcd
     WHERE  NOT EXISTS ( SELECT 1
                         FROM   dbo.MachineCluster mc INNER JOIN dbo.Machine cm
                                   ON mc.ClusterMachineId = cm.MachineId
                                INNER JOIN dbo.Machine mm
                                   ON mc.MemberMachineId = mm.MachineId
                                INNER JOIN tempdb.dbo.tempGetMachineInfoClusterToNode tcn
                                   ON tcn.ClusterName = cm.MachineName
                                  AND tcn.NodeName    = mm.MachineName
                         WHERE  cm.MachineId           = mcd.ClusterMachineId
                         AND    mm.MachineId           = mcd.MemberMachineId
                         AND    tcn.PackageExecutionId = @PackageExecutionId )
     -- BUT don't touch clusters that are not in the data set
     AND    EXISTS ( SELECT 1
                     FROM   tempdb.dbo.tempGetMachineInfoClusterToNode tcn
                            INNER JOIN dbo.Machine cm
                              ON tcn.ClusterName = cm.MachineName
                     WHERE  cm.MachineId = mcd.ClusterMachineId )

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- Volumes
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------

     -- Fill table variables
     INSERT @ClusterVolumeWithMountPoint
          ( MachineId,
            MachineName,
            ClusterVirtualNameFlag,
            PackageExecutionId,
            DiskId,
            ClusterDiskName,
            DiskPartitionPath,
            VolumeGUID,
            VolumeLabel,
            SerialNumber )
     SELECT DISTINCT 
            m.MachineId,
            m.MachineName,
            m.ClusterVirtualNameFlag,
            d.PackageExecutionId,
            DiskId          = d.Id,
            ClusterDiskName = d.NAME,
            dtdp.DiskPartitionPath,
            dp.VolumeGUID,
            dp.VolumeLabel,
            dp.SerialNumber
     FROM   dbo.Machine m INNER JOIN tempdb.dbo.tempGetMachineInfoClusterDisk d
               ON m.MachineName = d.MachineName
            INNER JOIN tempdb.dbo.tempGetMachineInfoClusterDiskToDiskPartition dtdp
               ON d.MachineName        = dtdp.MachineName 
              AND d.ID                 = dtdp.DiskID
              AND d.PackageExecutionId = dtdp.PackageExecutionID
            INNER JOIN tempdb.dbo.tempGetMachineInfoClusterDiskPartition dp
               ON dp.MachineName        = dtdp.MachineName
              AND dp.Path               = dtdp.DiskPartitionPath
              AND dp.PackageExecutionId = dp.PackageExecutionId
     WHERE  d.PackageExecutionId = @PackageExecutionId

     INSERT @VolumeWithMountPoint
          ( MachineId,
            MachineName,
            ClusterVirtualNameFlag,
            PackageExecutionId,
            DeviceID,
            Name,
            Label,
            BlockSize,
            Capacity,
            FreeSpace, 
            CompressedFlag,
            DriveTypeDesc,
            SerialNumber,
            FileSystem,
            MountPoint )
     SELECT DISTINCT 
            m.MachineId,
            m.MachineName,
            m.ClusterVirtualNameFlag,
            v.PackageExecutionId,
            v.DeviceID,
            v.Name,
            v.Label,
            v.BlockSize,
            v.Capacity,
            v.FreeSpace, 
            CompressedFlag = CASE v.Compressed WHEN 'FALSE' THEN 0 WHEN 'TRUE' THEN 1 ELSE NULL END,
            DriveTypeDesc = CASE v.DriveType WHEN 1 THEN 'No Root Directory'
                                             WHEN 2 THEN 'Removable Disk'
                                             WHEN 3 THEN 'Local Disk'
                                             WHEN 4 THEN 'Network Drive'
                                             WHEN 5 THEN 'Compact Disk'
                                             WHEN 6 THEN 'RAM Disk'
                                             ELSE 'Unknown'
                            END,
            v.SerialNumber,
            v.FileSystem,
            mp.MountPoint
     FROM   dbo.Machine m INNER JOIN tempdb.dbo.tempGetMachineInfoVolume v
               ON m.MachineName = v.MachineName
            LEFT OUTER JOIN tempdb.dbo.tempGetMachineInfoMountPoint mp
               ON v.DeviceID           = mp.DeviceID
              AND v.MachineName        = mp.MachineName
              AND v.PackageExecutionId = mp.PackageExecutionId
     WHERE  v.PackageExecutionId = @PackageExecutionId

     -- Do volumes for all machines, local and cluster
     DECLARE Volumes CURSOR FOR SELECT RecId
                                FROM   @VolumeWithMountPoint v
                                WHERE  NOT EXISTS ( SELECT 1
                                                    FROM   @ClusterVolumeWithMountPoint cv
                                                    WHERE  cv.SerialNumber  = v.SerialNumber
                                                    AND    cv.MachineId     = v.MachineId )
                                AND    v.ClusterVirtualNameFlag = 0
                                UNION ALL
                                SELECT v.RecId
                                FROM   @ClusterVolumeWithMountPoint cv
                                       INNER JOIN @VolumeWithMountPoint v
                                          ON cv.SerialNumber       = v.SerialNumber
                                         AND cv.MachineId          = v.MachineId
                                         AND cv.PackageExecutionId = v.PackageExecutionId
                                WHERE  cv.PackageExecutionId = @PackageExecutionId
                                AND    cv.ClusterVirtualNameFlag = 1

     OPEN Volumes
     
     FETCH NEXT FROM Volumes INTO @RecId
     
     WHILE ( @@FETCH_STATUS = 0 )
     BEGIN
          SELECT @ClusterFlag             = v.ClusterVirtualNameFlag,
                 @MachineId               = v.MachineId,
                 @VolumeDeviceId          = v.DeviceId,
                 @VolumeMountPoint        = v.MountPoint,
                 @VolumeBlockSize         = v.BlockSize,
                 @VolumeCapacity          = v.Capacity,
                 @VolumeFreeSpace         = v.FreeSpace,
                 @VolumeCompressedFlag    = v.CompressedFlag,
                 @VolumeDriveTypeDesc     = v.DriveTypeDesc,
                 @VolumeFileSystemType    = v.FileSystem,
                 @VolumeLabel             = v.Label,
                 @VolumeName              = v.Name,
                 @VolumeSerialNumber      = v.SerialNumber
          FROM   @VolumeWithMountPoint v
          WHERE  v.RecId = @RecId

          EXEC dbo.SaveMachineDriveVolume
                    @MachineId               = @MachineId,
                    @PackageExecutionId      = @PackageExecutionId,
                    @VolumeDeviceId          = @VolumeDeviceId,
                    @VolumeMountPoint        = @VolumeMountPoint,
                    @VolumeBlockSize         = @VolumeBlockSize,
                    @VolumeCapacity          = @VolumeCapacity,
                    @VolumeCompressedFlag    = @VolumeCompressedFlag,
                    @VolumeDriveTypeDesc     = @VolumeDriveTypeDesc,
                    @VolumeFileSystemType    = @VolumeFileSystemType,
                    @VolumeLabel             = @VolumeLabel,
                    @VolumeName              = @VolumeName,
                    @VolumeSerialNumber      = @VolumeSerialNumber,
                    @ClusterSharedVolumeFlag = @ClusterFlag,
                    @VolumeDeletedFlag       = 0,
                    @MachineDriveVolumeId    = @MachineDriveVolumeId OUTPUT
     
          EXEC dbo.InsMachineDriveVolumeSpace
                    @MachineId            = @MachineId,
                    @PackageExecutionId   = @PackageExecutionId,
                    @MachineDriveVolumeId = @MachineDriveVolumeId,
                    @VolumeDeviceId       = @VolumeDeviceId,
                    @VolumeCapacity       = @VolumeCapacity,
                    @VolumeFreeSpace      = @VolumeFreeSpace
          
          FETCH NEXT FROM Volumes INTO @RecId
     END

     CLOSE Volumes
     DEALLOCATE Volumes

     -- Perform Volume "Deletions"
     DECLARE VolumeDeletes CURSOR FOR SELECT dv.MachineId, dv.VolumeDeviceId -- , dv.MachineDriveVolumeId
                                      FROM   dbo.MachineDriveVolume dv
                                      WHERE  NOT EXISTS ( SELECT 1
                                                          FROM   @VolumeWithMountPoint v
                                                          WHERE  v.MachineId = dv.MachineId
                                                          AND    v.DeviceId  = dv.VolumeDeviceId )
                                      -- But only for machines in the data set, don't delete others.
                                      AND    EXISTS ( SELECT 1
                                                      FROM   @VolumeWithMountPoint v
                                                      WHERE  v.MachineId = dv.MachineId )

     OPEN VolumeDeletes

     FETCH NEXT FROM VolumeDeletes INTO @MachineId, @VolumeDeviceId -- , @MachineDriveVolumeId

     WHILE ( @@FETCH_STATUS = 0 )
     BEGIN
          EXEC dbo.SaveMachineDriveVolume
                    @MachineId               = @MachineId,
                    @PackageExecutionId      = @PackageExecutionId,
                    @VolumeDeviceId          = @VolumeDeviceId,
                    @VolumeMountPoint        = NULL,
                    @VolumeBlockSize         = NULL,
                    @VolumeCapacity          = NULL,
                    @VolumeCompressedFlag    = NULL,
                    @VolumeDriveTypeDesc     = NULL,
                    @VolumeFileSystemType    = NULL,
                    @VolumeLabel             = NULL,
                    @VolumeName              = NULL,
                    @VolumeSerialNumber      = NULL,
                    @ClusterSharedVolumeFlag = NULL,
                    @VolumeDeletedFlag      = 1

          FETCH NEXT FROM VolumeDeletes INTO @MachineId, @VolumeDeviceId -- , @MachineDriveVolumeId
     END

     CLOSE VolumeDeletes
     DEALLOCATE VolumeDeletes

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- Environment Variables
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------

     -- Fill Table Variables
     INSERT @EnvVar
          ( MachineId,
            MachineName,
            ClusterVirtualNameFlag,
            PackageExecutionId,
            VarName,
            VarUser,
            VarValue )
     SELECT DISTINCT
            m.MachineId,
            m.MachineName,
            m.ClusterVirtualNameFlag,
            e.PackageExecutionId,
            e.VarName,
            e.VarUser,
            e.VarValue
     FROM   dbo.Machine m INNER JOIN tempdb.dbo.tempGetMachineInfoEnvVar e
               ON m.MachineName = e.MachineName
     WHERE  e.PackageExecutionId = @PackageExecutionId

     DECLARE EnvVars CURSOR FOR SELECT RecId
                                FROM   @EnvVar
                                WHERE  ClusterVirtualNameFlag = 0 -- No cluster vnames

     OPEN EnvVars
     
     FETCH NEXT FROM EnvVars INTO @RecId
     
     WHILE ( @@FETCH_STATUS = 0 )
     BEGIN
          SELECT @ClusterFlag = e.ClusterVirtualNameFlag,
                 @MachineId   = e.MachineId,
                 @VarName     = e.VarName,
                 @VarUser     = e.VarUser,
                 @VarValue    = e.VarValue
          FROM   @EnvVar e
          WHERE  e.RecId = @RecId

          EXEC dbo.SaveMachineEnvVar
                    @MachineId          = @MachineId,
                    @PackageExecutionId = @PackageExecutionId,
                    @VarName            = @VarName,
                    @VarUser            = @VarUser,
                    @VarVal             = @VarValue,
                    @VarDeletedFlag     = 0
          FETCH NEXT FROM EnvVars INTO @RecId
     END

     CLOSE EnvVars
     DEALLOCATE EnvVars

     -- Perform cluster "deletes", any machine that is a cluster vname will have
     -- any defined environment variables removed.  This process will not insert
     -- then anyway, however, in the odd case that a stand alone machine existed
     -- and then that name was used for a cluster vname this handles it.  We also
     -- handle regular deletes here since the logic is effectively the same.
     DECLARE EnvVarsDelete CURSOR FOR SELECT mev.MachineId, mev.VarName, mev.VarUser
                                      FROM   dbo.Machine m INNER JOIN dbo.MachineEnvVar mev
                                                ON m.MachineId = mev.MachineId
                                      WHERE  m.ClusterVirtualNameFlag = 1
                                      UNION ALL
                                      -- Handle Regular deletes too..
                                      SELECT mev.MachineId, mev.VarName, mev.VarUser
                                      FROM   dbo.MachineEnvVar mev
                                      WHERE  NOT EXISTS ( SELECT 1
                                                          FROM   @EnvVar e
                                                          WHERE  mev.MachineId = e.MachineId
                                                          AND    mev.VarName   = e.VarName
                                                          AND    mev.VarUser   = e.VarUser ) 
                                      -- Don't touch machines not in the data
                                      AND    EXISTS ( SELECT 1
                                                      FROM   @EnvVar e
                                                      WHERE  e.MachineId = mev.MachineId )

     OPEN EnvVarsDelete
     
     FETCH NEXT FROM EnvVarsDelete INTO @MachineId, @VarName, @VarUser

     WHILE ( @@FETCH_STATUS = 0 )
     BEGIN
          EXEC dbo.SaveMachineEnvVar
                    @MachineId          = @MachineId,
                    @PackageExecutionId = @PackageExecutionId,
                    @VarName            = @VarName,
                    @VarUser            = @VarUser,
                    @VarVal             = NULL,
                    @VarDeletedFlag     = 1

          FETCH NEXT FROM EnvVarsDelete INTO @MachineId, @VarName, @VarUser
     END

     CLOSE EnvVarsDelete
     DEALLOCATE EnvVarsDelete

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- End
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------

     -- Consolidate Records
     INSERT @MissingNodes
          ( MachineId,
            MachineName )
     SELECT MachineId,
            MachineName
     FROM   @MissingClusters
     ORDER BY MachineId

     -- Give result set of missing nodes for next pass.
     SELECT @RV = COUNT(*) FROM @MissingNodes

     SELECT MachineId, MachineName FROM @MissingNodes

     RETURN @RV
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Due to the complexity of determinations about clusters, cluster nodes, cluster drive volumes, and such, this sproc handles the logic needed to add any extra machines.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveMachineInfoFromWMI;
GO
